﻿namespace FlashPay.Service.Impl.Report
{
    using AutoMapper;
    using FlashPay.DAO.Interface;
    using FlashPay.DAO.Report;
    using FlashPay.EF;
    using FlashPay.EF.Models;
    using FlashPay.Entity;
    using FlashPay.Entity.Enum;
    using FlashPay.Entity.Parameter;
    using FlashPay.Entity.Request.Report;
    using FlashPay.Entity.Response.Bank;
    using FlashPay.Entity.Response.BankCard;
    using FlashPay.Entity.Response.Company;
    using FlashPay.Entity.Response.Report;
    using FlashPay.Service.Report;
    using FlashPay.Util;
    using Microsoft.EntityFrameworkCore;
    using MySql.Data.MySqlClient;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Linq;
    using System.Threading.Tasks;

    /// <summary>
    /// 报表业务接口实现
    /// </summary>
    public class ReportServiceImpl : ReportService
    {
        /// <summary>
        /// 报表数据接口
        /// </summary>
        private readonly ReportStatisticsDao _ReportStatisticsDao;

        /// <summary>
        /// 公司数据接口
        /// </summary>
        private readonly CompanyDao _companyDao;

        /// <summary>
        /// 银行数据接口
        /// </summary>
        private readonly BankDao _bankDao;

        /// <summary>
        /// mapper
        /// </summary>
        private readonly IMapper _mapper;

        /// <summary>
        /// 上下文
        /// </summary>
        private FlashPayContext _context { set; get; }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="reportStatisticsDao">报表数据接口</param>
        /// <param name="mapper">Mapper</param>
        public ReportServiceImpl(ReportStatisticsDao reportStatisticsDao, CompanyDao companyDao, BankDao bankDao, IMapper mapper, FlashPayContext context)
        {
            _ReportStatisticsDao = reportStatisticsDao;
            _companyDao = companyDao;
            _bankDao = bankDao;
            _mapper = mapper;
            _context = context;
        }

        #region 获取月报表x轴
        /// <summary>
        /// 获取月报表x轴
        /// </summary>
        /// <returns></returns>
        private XAxis GetMonthsXAxis(DateTime dt)
        {
            //当前时间
            var now = DateTime.Now;
            //获取月天数
            int days = DateTime.DaysInMonth(dt.Year, dt.Month);
            if (now.Year == dt.Year && now.Month == dt.Month)
            {
                days = days - (days - now.Day);
            }

            var ids = new List<int>();

            var categories = new List<string>();

            for (int i = 1; i <= days; i++)
            {

                ids.Add(i);

                categories.Add(string.Format("{0}{1}", dt.ToString("MM-"), i.ToString("00")));
            }

            return new XAxis
            {
                Ids = ids,
                Categories = categories
            };
        }
        #endregion

        #region 根据编号获取公司
        /// <summary>
        /// 根据编号获取公司
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>Company</returns>
        private List<CompanyResponse> GetCompany(int companyId, int level=2)
        {
            var companyRequest = new Company()
            {
                CompanyId = companyId,
                CompanyPid = -1,
                CompanyStatus = 1
            };

            var companyList = new BaseModel<List<Company>>();

            _companyDao.Get(companyList, companyRequest, level);
            var companyResponse = new List<CompanyResponse>();
            if (companyList.Success)
            {
                foreach (var item in companyList.Result)
                {
                    companyResponse.Add(new CompanyResponse()
                    {
                        CompanyID = item.CompanyId,
                        CompanyName = item.CompanyName,
                        CompanyStatus = item.CompanyStatus
                    });
                }
            }
            return companyResponse;
        }
        #endregion

        #region Y轴计算
        private void YAxis(ReportResponse reportResponse, decimal max)
        {
            if (max < 10)
            {
                reportResponse.YAxis.Interval = 2;
            }
            else if (max >= 10 && max < 100)
            {
                reportResponse.YAxis.Interval = 10;
            }
            else if (max >= 100 && max < 1000)
            {
                reportResponse.YAxis.Interval = 100;
            }
            else if (max >= 1000 && max < 10000)
            {
                reportResponse.YAxis.Interval = 1000;
            }
            else if (max >= 10000 && max < 100000)
            {
                reportResponse.YAxis.Interval = 10000;
            }
            else if (max >= 100000 && max < 1000000)
            {
                reportResponse.YAxis.Interval = 100000;
            }
            else if (max >= 1000000 && max < 10000000)
            {
                reportResponse.YAxis.Interval = 1000000;
            }
            else if (max >= 10000000 && max < 100000000)
            {
                reportResponse.YAxis.Interval =  10000000;
            }
            else if (max >= 100000000 && max < 1000000000)
            {
                reportResponse.YAxis.Interval = 100000000;
            }
            else if (max >= 1000000000 && max < 10000000000)
            {
                reportResponse.YAxis.Interval = 1000000000;
            }
            else if (max >= 10000000000 && max < 100000000000)
            {
                reportResponse.YAxis.Interval = 10000000000;
            }
        }

        //后续要优化
        private YAxis YAxis(long max)
        {
            var yA = new YAxis { Max = max };

            long nums = 0;
            string unit = string.Empty;
            string newUnit = string.Empty;

            if (max < 10)
            {
                nums = 1;
                newUnit = "0";
            }
            else if (max >= 10 && max < 100)
            {
                nums = 10;
                unit = "0"; newUnit = "00";
            }
            else if (max >= 100 && max < 1000)
            {
                nums = 100;
                unit = "00"; newUnit = "000";
            }
            else if (max >= 1000 && max < 10000)
            {
                nums = 1000;
                unit = "000";newUnit = "0000";
            }
            else if (max >= 10000 && max < 100000)
            {
                nums = 10000;
                unit = "万"; newUnit = "0万";
            }
            else if (max >= 100000 && max < 1000000)
            {
                nums = 100000;
                unit = "0万"; newUnit = "00万";
            }
            else if (max >= 1000000 && max < 10000000)
            {
                nums = 1000000;
                unit = "00万"; newUnit = "000万";
            }
            else if (max >= 10000000 && max < 100000000)
            {
                nums = 10000000;
                unit = "千万"; newUnit = "亿";
            }
            else if (max >= 100000000 && max < 1000000000)
            {
                nums = 100000000;
                unit = "亿"; newUnit = "0亿";
            }
            else if (max >= 1000000000 && max <= 10000000000)
            {
                nums = 1000000000;
                unit = "0亿"; newUnit = "00亿";
            }
            else
            {
                nums = 10000000000;
                unit = "00亿"; newUnit = "000亿";
            }

            yA.Interval = nums;
            yA.Unit = unit;
            yA.NextUnit = newUnit;

            return yA;
        }
        #endregion

        #region 获取公司月份报表
        /// <summary>
        /// 获取公司月份报表
        /// </summary>
        /// <param name="request">请求参数</param>
        public JResult<ReportResponse> GetCompanyMonthsReport(CompanyMonthsReportRequest request)
        {
            var response = new JResult<ReportResponse>()
            {
                Success = true
            };

            var now = DateTime.Now;

            if (request.ReportDate.Year > now.Year)
            {
                response.Success = false;
                response.ErrorMessage = "选择的年份无效！";
                return response;
            }

            if (request.ReportDate.Year == now.Year && request.ReportDate.Month > now.Month)
            {
                response.Success = false;
                response.ErrorMessage = "选择的月份无效！";
                return response;
            }

            var reportTypes = new List<sbyte>() { 1, 2 };
            if (!reportTypes.Contains(request.ReportType))
            {
                response.Success = false;
                response.ErrorMessage = "类型错误！";
                return response;
            }

            var typeName = request.ReportType.Equals(1) ? "收款" : "付款";

            var reportResponse = new ReportResponse()
            {
                Title = request.ReportDate.ToString($"yyyy年MM月公司{typeName}报表"),
                XAxis = GetMonthsXAxis(request.ReportDate),
                YAxis = new YAxis()
                {
                    Min = 0,
                    Max = 100000,
                    LineWidth = 1,
                    LineColor = "#c0c0c0",
                    Interval = 500
                }
            };

            #region 母公司及子公司查找
            var query = new ReportStatisticsQuery();
            query.ReportType = request.ReportType;
            query.StartTime = request.StartTime;
            query.EndTime = request.EndTime;
            query.PaymentType = request.PaymentType;

            if (request.CompanyIds == null)
            {
                query.CompanyIds = _bankDao.GetCompanyIDList(request.CompanyId);
            }
            else {
                query.CompanyIds = request.CompanyIds;
            }
            //query.CardBcid = 0;
            #endregion

            //系数
            var series = new List<Series>();

            if (reportResponse.XAxis.Ids != null && reportResponse.XAxis.Ids.Any())
            {
                var list = _ReportStatisticsDao.GetReportByMonth(query);
                if (list.Count<=0)
                {
                    response.Success = false;
                    response.ErrorMessage = "暂无数据！";
                    return response;
                }

                #region 金额、数量
                //金额
                var serieAmount = new Series();
                serieAmount.Name = "金额";
                serieAmount.Color = "#f66930";
                //数量
                var serieQuantity = new Series();
                serieQuantity.Name = "笔数";
                serieQuantity.Color = "#3CB371";

                var dataAmount = new List<decimal>();
                var dataQuantity = new List<decimal>();
                List<decimal> numList = new List<decimal>();

                if (list != null && list.Any())
                {
                    var r = new Random();

                    foreach (var item in reportResponse.XAxis.Ids)
                    {
                        var dayReport = list.Where(c => c.Day == item).FirstOrDefault();
                        if (dayReport!=null)
                        {
                            numList.Add(dayReport.TotalAmount);
                            numList.Add(dayReport.TotalQuantity);

                            dataAmount.Add(dayReport.TotalAmount);
                            dataQuantity.Add(dayReport.TotalQuantity);
                        }
                        else
                        {
                            dataAmount.Add(0);
                            dataQuantity.Add(0);
                        }
                    }
                    serieAmount.Data = dataAmount;
                    serieQuantity.Data = dataQuantity;
                }
                #endregion

                series.Add(serieAmount);
                series.Add(serieQuantity);

                var reportDayMaxAmount = numList.OrderByDescending(p => p).FirstOrDefault();
                reportResponse.YAxis = YAxis((long)reportDayMaxAmount);

                reportResponse.Series = series;
            }
            response.Data = reportResponse;

            return response;
        }

        public List<int> GetCompanyIds(int CompanyId)
        {
            return _bankDao.GetCompanyIDList(CompanyId);
        }
        #endregion

        #region 获取银行卡月份报表
        /// <summary>
        /// 获取银行卡月份报表
        /// </summary>
        /// <param name="request">请求参数</param>
        public JResult<ReportResponse> GetBankCardMonthsReport(BankCardMonthsReportReqeust request)
        {
            var response = new JResult<ReportResponse>()
            {
                Success = true
            };

            var now = DateTime.Now;

            if (request.ReportDate.Year > now.Year)
            {
                response.Success = false;
                response.ErrorMessage = "选择的年份无效！";
                return response;
            }

            if (request.ReportDate.Year == now.Year && request.ReportDate.Month > now.Month)
            {
                response.Success = false;
                response.ErrorMessage = "选择的月份无效！";
                return response;
            }

            var reportTypes = new List<sbyte>() { 1, 2 };
            if (!reportTypes.Contains(request.ReportType))
            {
                response.Success = false;
                response.ErrorMessage = "类型错误！";
                return response;
            }

            var typeName = request.ReportType.Equals(1) ? "收款" : "付款";

            var reportResponse = new ReportResponse()
            {
                Title = request.ReportDate.ToString($"yyyy年MM月银行卡{typeName}报表"),
                XAxis = GetMonthsXAxis(request.ReportDate),
                YAxis = new YAxis()
                {
                    Min = 0,
                    Max = 100000,
                    LineWidth = 1,
                    LineColor = "#c0c0c0",
                    Interval = 500
                }
            };
            //母公司及子公司查找
            var query = new ReportStatisticsQuery();
            query.ReportType = request.ReportType;
            query.StartTime = request.StartTime;
            query.EndTime = request.EndTime;
            query.CompanyIds = _bankDao.GetCompanyIDList(request.CompanyId);
            query.CardBcid = 0;
            //系数
            var series = new List<Series>();

            if (reportResponse.XAxis.Ids != null && reportResponse.XAxis.Ids.Any())
            {
                var list = this.GetList(query);

                #region 金额、数量
                //金额
                var serieAmount = new Series();
                serieAmount.Name = "金额";
                serieAmount.Color = "#f66930";
                //数量
                var serieQuantity = new Series();
                serieQuantity.Name = "笔数";
                serieQuantity.Color = "#3CB371";

                var dataAmount = new List<decimal>();
                var dataQuantity = new List<decimal>();

                if (list != null && list.Any())
                {
                    var r = new Random();

                    foreach (var item in reportResponse.XAxis.Ids)
                    {
                        var day = item.ToString("00");
                        var startTime = request.ReportDate.ToString($"yyyy-MM-{day} 00:00:00");
                        var endTime = request.ReportDate.ToString($"yyyy-MM-{day} 23:59:59");

                        var data = list.Where(c => c.ReportDate >= Convert.ToDateTime(startTime) && c.ReportDate <= Convert.ToDateTime(endTime)).ToList();

                        dataAmount.Add(r.Next(1, 500));
                        dataQuantity.Add(data.Sum(c => c.ReportQuantity));
                    }

                    serieAmount.Data = dataAmount;
                    serieQuantity.Data = dataQuantity;
                }
                #endregion

                series.Add(serieAmount);
                series.Add(serieQuantity);

                reportResponse.Series = series;
            }
            response.Data = reportResponse;

            return response;
        }
        #endregion

        #region 获取公司年报表
        /// <summary>
        /// 获取所有报表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns>List<UserInfo></returns>
        public List<ReportStatisticsResponse> GetList(ReportStatisticsQuery query)
        {
            var list = _ReportStatisticsDao.GetList(query);
            if (list != null && list.Any())
                return _mapper.Map<List<ReportStatisticsResponse>>(list);
            else
                return null;
        }

        /// <summary>
        /// 获取年报表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns>List<UserInfo></returns>
        public JResult<ReportResponse> GetReportByYear(ReportStatisticsQuery query)
        {
            var response = new JResult<ReportResponse>()
            {
                Success = true
            };

            //添加验证
            if (query.CompanyId.HasValue)
            {
                query.CompanyIds = _bankDao.GetCompanyIDList(query.CompanyId.Value);
            }

            var list = _ReportStatisticsDao.GetReportByYear(query);

            bool isNot = true;
            if (list==null)
            {
                isNot = false;
            }
            if (list.Count == 0)
            {
                isNot = false;
            }

            if (!isNot)
            {
                response.Success = false;
                response.ErrorMessage = "暂时没有数据";
                return response;
            }

            try
            {
                List<decimal> numList = new List<decimal>();
                List<decimal> dataAmount = new List<decimal>();
                List<decimal> dataQuantity = new List<decimal>();
                for (int i = 0; i < 12; i++)
                {
                    dataAmount.Add(0); dataQuantity.Add(0);
                }
                for (int z = 0; z < list.Count; z++)
                {
                    if (list[z].Year > 0)
                    {
                        var totalAmount = list[z].TotalAmount;
                        var totalQuantity = list[z].TotalQuantity;

                        numList.Add(totalAmount);
                        numList.Add(totalQuantity);

                        dataAmount[list[z].Year - 1] = totalAmount;
                        dataQuantity[list[z].Year - 1] = totalQuantity;
                    }
                }

                //系数
                var series = GetSeriewList(dataAmount, dataQuantity);

                long maxNum = 0;
                if (numList.Count > 0)
                {
                    numList.Sort((a, b) => b.CompareTo(a));//从大到小排序
                    maxNum=(long)numList.FirstOrDefault();
                }

                var reportResponse = new ReportResponse()
                {
                    Title = query.StartTime + "年报表",
                    XAxis = GetXAxis(DateTime.Now, 1),
                    YAxis = new YAxis{  },
                };
                reportResponse.YAxis= YAxis(maxNum);
                reportResponse.Series = series;
                response.Success = true;
                response.Data = reportResponse;
            }
            catch (Exception)
            {
                response.Success = false;
                response.ErrorMessage = "获取年报表失败";
            }

            return response;
        }

        /// <summary>
        /// 获取年报表x轴
        /// </summary>
        /// <returns></returns>
        private XAxis GetXAxis(DateTime dt, int type = 0)
        {
            //当前时间
            var now = DateTime.Now;

            int days = 12;
            if (type == 0)
            {
                //获取月天数
                days = DateTime.DaysInMonth(dt.Year, dt.Month);
                if (now.Year == dt.Year && now.Month == dt.Month)
                {
                    days = days - (days - now.Day);
                }
            }

            var ids = new List<int>();
            var categories = new List<string>();
            for (int i = 1; i <= days; i++)
            {
                ids.Add(i);
                if (type == 1)
                {
                    categories.Add(string.Format("{0}{1}", i, "月"));
                }
                else
                {
                    categories.Add(string.Format("{0}{1}", dt.ToString("MM-"), i.ToString("00")));
                }
            }

            return new XAxis
            {
                Ids = ids,
                Categories = categories
            };
        }

        //获取系数集合
        private List<Series> GetSeriewList(List<decimal> dataAmount, List<decimal> dataQuantity)
        {
            //金额
            var serieAmount = new Series();
            serieAmount.Name = "金额";
            serieAmount.Color = "#f66930";
            serieAmount.Data = dataAmount;

            //数量
            var serieQuantity = new Series();
            serieQuantity.Name = "笔数";
            serieQuantity.Color = "#3CB371";
            serieQuantity.Data = dataQuantity;

            //系数
            var series = new List<Series>();
            series.Add(serieAmount);
            series.Add(serieQuantity);

            return series;
        }
        #endregion

        #region 银行卡明细报表
        /// <summary>
        /// 银行卡明细报表
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public List<BankCardDetailResponse> GetBankCardDetail(BankCardDetailQuery query)
        {
            var response = new List<BankCardDetailResponse>();
            //获取所有公司
            var companys = _companyDao.GetList(new CompanyQuery() { CompanyIds = query.CompanyIds });
            //获取银行卡
            var banks = _bankDao.GetList();
            //获取报表
            var reportStatisticss = _ReportStatisticsDao.GetList(new ReportStatisticsQuery()
            {
                ReportType = query.ReportType,
                StartTime = query.StartTime,
                EndTime = query.EndTime
            });

            //获取公司
            var company = companys.FirstOrDefault(p => p.CompanyId == query.CompanyId);

            response.Add(new BankCardDetailResponse()
            {
                CompanyId = company.CompanyId,
                CompanyName = company != null ? company.CompanyName : "",
                DepositNumbr = 0,
                DepositSum = 0
            });

            //获取子公司
            var companyIds = GetCompany(query.CompanyId).Select(p=>p.CompanyID).ToList();
            //获取报表数据
            var reportStatistics = _ReportStatisticsDao.GetReportByCompanyId(new ReportStatisticsQuery() {
                ReportType = query.ReportType,
                CompanyIds = companyIds,
                StartTime = query.StartTime,
                EndTime = query.EndTime
            });

            if (reportStatistics != null && reportStatistics.Any())
            {
                //存款成功次数
                var depositNumbr = 0;
                //存款成功总额
                var depositSum = new decimal(0);

                var firtResponse = new List<BankCardDetailResponse>();

                int index = 1;

                foreach (var item in reportStatistics)
                {
                    if (!query.CompanyIds.Contains(item.CompanyId))
                    {
                        continue;
                    }
                    //获取子公司
                    var model = companys.FirstOrDefault(p => p.CompanyId == item.CompanyId);

                    var totalQuantity = item.TotalQuantity;
                    var totalAmount = item.TotalAmount;

                    //添加到列表
                    var childrenResponse = new BankCardDetailResponse()
                    {
                        Index = index,
                        CompanyId = item.CompanyId,
                        CompanyName = model != null ? model.CompanyName : "",
                        DepositNumbr = Convert.ToInt32(totalQuantity),
                        DepositSum = totalAmount
                    };
                    firtResponse.Add(childrenResponse);
                    //各和
                    depositNumbr += Convert.ToInt32(totalQuantity);
                    depositSum += totalAmount;
                    //获取该公司银行卡
                    this.GetBankCard(childrenResponse, companys, banks, reportStatisticss, query,index);
                    index++;
                }

                response[0].DepositNumbr = depositNumbr;
                response[0].DepositSum = depositSum;

                response[0].Children = firtResponse;
            }
            return response;
        }

        /// <summary>
        /// 获取公司银行卡
        /// </summary>
        /// <param name="Response"></param>
        /// <param name="companys"></param>
        /// <param name="query"></param>
        private void GetBankCard(BankCardDetailResponse response, List<Company> companys, List<BankPagerResponse> banks, List<ReportStatistics> reportStatistics, BankCardDetailQuery query,int index)
        {
            var companyBankCardResponses = new List<BankCardDetailResponse>();
            //获取报表数据
            var list = _ReportStatisticsDao.GetReportByCardBcId(new ReportStatisticsQuery()
            {
                ReportType = query.ReportType,
                CompanyIds = new List<int>() { response.CompanyId },
                StartTime = query.StartTime,
                EndTime = query.EndTime
            });
            if (list != null && list.Any())
            {
                index = index * 1000;
                //存款成功次数
                var depositNumbr = 0;
                //存款成功总额
                var depositSum = new decimal(0);

                foreach (var item in list)
                {
                    //获取银行卡
                    var model = banks.FirstOrDefault(p => p.BcId == item.CardBcid);

                    var childrenResponse = new BankCardDetailResponse()
                    {
                        Index = index,
                        CompanyId = item.CompanyId,
                        CompanyName = $"{model.BankName}-{model.CardName}",
                        DepositNumbr = Convert.ToInt32(item.TotalQuantity),
                        DepositSum = item.TotalAmount
                    };
                    depositNumbr += childrenResponse.DepositNumbr;
                    depositSum += childrenResponse.DepositSum;

                    childrenResponse.DepositSumNew = string.Format("{0:N}", childrenResponse.DepositSum);
                    companyBankCardResponses.Add(childrenResponse);
                    index++;
                }
            }
            if (companyBankCardResponses != null && companyBankCardResponses.Any())
            {
                response.Children = companyBankCardResponses;
            }
        }
        #endregion

        #region 获取最后更新时间
        /// <summary>
        /// 获取最后更新时间
        /// </summary>
        /// <returns></returns>
        public JResult<string> GetLastUpdateDate()
        {
            return _ReportStatisticsDao.GetLastUpdateDate();
        }
        #endregion

        #region 银行明细报表
        /// <summary>
        /// 银行明细报表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public DataGrid<BankDetailResponse> BankDetail(BankDetailRequest query)
        {
            var response = new DataGrid<BankDetailResponse>() {
                Success = false,
            };

            if (string.IsNullOrEmpty(query.CardNumber)) {
                return new DataGrid<BankDetailResponse>()
                {
                    Total = 0,
                    Rows = new List<BankDetailResponse>() { },
                    Success = false,
                    ErrorMessage = "请输入银行卡号."
                };
            }

            if (Convert.ToDateTime(query.StartTime) < DateTime.Now.AddMonths(-1))
            {
                return new DataGrid<BankDetailResponse>()
                {
                    Total = 0,
                    Rows = new List<BankDetailResponse>() { },
                    Success = false,
                    ErrorMessage = "只能查近一个月的一交易记录."
                };
            }

            var connection = _context.Database.GetDbConnection();

            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            
            //获取所有公司
            var companys = _bankDao.GetCompanyIDList(query.CompanyId);
            //获取银行卡
            var bank = _context.BankCard.Where(c => companys.Contains(c.CompanyId) && c.CardNumber == query.CardNumber).FirstOrDefault();

            if (bank == null) {

                return new DataGrid<BankDetailResponse>()
                {
                    Total = 0,
                    Rows = new List<BankDetailResponse>() { },
                    Success = false,
                    ErrorMessage = "银卡卡号不存在."
                };
            }

            var bankDetailResponse = new List<BankDetailResponse>();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "call sp_SelectDepositRecordAndRecordRealWeb(@id,@startTime, @endTime);";
                command.Parameters.Add(new MySqlParameter("id", bank.Bcid));
                command.Parameters.Add(new MySqlParameter("startTime", query.StartTime));
                command.Parameters.Add(new MySqlParameter("endTime", query.EndTime));

                DbDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        var operatingDate = reader.GetFieldValue<DateTime>(0);
                        var createDBDate = reader.GetFieldValue<DateTime>(1);
                        var recordType = reader.GetFieldValue<Int64>(2);
                        var amount = reader.GetFieldValue<decimal>(3);
                        var afterBalance = reader.GetFieldValue<decimal>(4);
                        var cardName = reader.GetFieldValue<string>(5);
                        var bankName = reader.GetFieldValue<string>(6);
                        var clientBankName = reader.GetFieldValue<string>(7);
                        var clientAccountName = reader.GetFieldValue<string>(8);
                        var clientCardNumber = reader.GetFieldValue<string>(9);

                        bankDetailResponse.Add(new BankDetailResponse() {
                            OperatingDate = reader.GetFieldValue<DateTime>(0),
                            CreateDBDate = reader.GetFieldValue<DateTime>(1),
                            RecordType = reader.GetFieldValue<Int64>(2),
                            Amount = reader.GetFieldValue<decimal>(3),
                            Balance = reader.GetFieldValue<decimal>(4),
                            CardName = reader.GetFieldValue<string>(5),
                            BankName = reader.GetFieldValue<string>(6),
                            ClientBankName = reader.GetFieldValue<string>(7),
                            ClientAccountName = reader.GetFieldValue<string>(8),
                            ClientCardNumber = reader.GetFieldValue<string>(9)
                        });
                    }
                }
            }

            connection.Close();

            if (bankDetailResponse.Any())
            {
                var list = bankDetailResponse.OrderByDescending(c => c.OperatingDate).Skip((query.Page.Value - 1) * query.Rows.Value).Take(query.Rows.Value).ToList();
                var total = bankDetailResponse.Count();

                list.ForEach(item =>
                {
                    item.OperatingDateText = item.OperatingDate.ToString("yyyy-MM-dd HH:mm:ss");
                    item.CreateDBDateText = item.CreateDBDate.ToString("yyyy-MM-dd HH:mm:ss");
                });

                return new DataGrid<BankDetailResponse>()
                {
                    Total = total,
                    Rows = list,
                    Success = true
                };
            }
            else
            {
                return new DataGrid<BankDetailResponse>()
                {
                    Total = 0,
                    Rows = new List<BankDetailResponse>() { },
                    Success = false,
                    ErrorMessage = "暂无查询记录."
                };
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="companyId"></param>
        public JResult<List<BankCardResponse>> GetBankCardListByCompanyId(int companyId)
        {
            var response = new JResult<List<BankCardResponse>>() {
                Success = true
            };
            //获取所有公司
            var companys = _bankDao.GetCompanyIDList(companyId);
            //获取银行卡
            var banks = _context.BankCard.Where(c => companys.Contains(c.CompanyId) && c.PaymentType ==0).ToList();

            var bankCardResponse = new List<BankCardResponse>();

            banks.ForEach(item => {
                bankCardResponse.Add(new BankCardResponse() {
                    Bcid = item.Bcid,
                    CardName = item.CardName,
                    CardNumber = item.CardNumber
                });
            });

            response.Data = bankCardResponse;

            return response;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="companyId"></param>
        public JResult CardNumberExit(int companyId,string cardNumber)
        {
            var response = new JResult()
            {
                Success = false
            };
            //获取所有公司
            var companys = _bankDao.GetCompanyIDList(companyId);
            //获取银行卡
            var bankCard = _context.BankCard.Where(c => companys.Contains(c.CompanyId) && c.CardNumber == cardNumber).FirstOrDefault();
            if (bankCard != null) {
                response.Success = true;
            }
            return response;
        }
        #endregion
    }
}